#! /bin/bash

if [ -n "$1" ];then
    data_date="$1"
else
    data_date=$(date -d " -1 days" +%F)
fi

ads_load_data_sql="insert into table jtp_app_ads.ads_traffic_page_view_report
                   SELECT
                       '2024-11-18' AS dt
                        , recent_days as recent_days
                        , province
                        , city
                        , sum(session_pv) AS page_view_count
                        , count(DISTINCT device_id) AS user_count
                        , count(session_id) AS session_count
                        , round(count(session_id) /count(DISTINCT device_id) , 2) AS avg_user_session_count
                        , round(sum(session_during_time) / 1000 / 60, 2) AS session_during_time
                        , round(avg(session_during_time) / 1000 / 60, 2) AS avg_session_during_time
                        , sum(if(session_pv = 1, 1, 0)) AS bounce_count
                        , round(sum(if(session_pv = 1, 1, 0)) / count(session_pv) , 4) AS bounce_count_rate
                   FROM jtp_app_dws.dws_event_log_session_agg
                   lateral view explode(array(1,3,7)) recent_days as recent_days
                   WHERE dt >= date_sub('2024-11-18',6) and dt <= '2024-11-18' AND province IS NOT NULL  AND city IS NOT NULL
                   GROUP BY province, recent_days, city;"


/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ads_load_data_sql}"